------------------
-- 需求说明
------------------
-- 设计简易版的银行数据库表结构，要求可以完成以下基本功能
-- 银行开户（注册个人信息）及开卡（办理银行卡）（一个人可以办理多张银行卡，但最多办理三张）
-- 存钱
-- 查询余额
-- 取钱
-- 转账
-- 查看交易记录
-- 账户挂失
-- 账户注销

------------------
-- 表结构说明
------------------
-- 账户信息表
-- 银行卡表
-- 交易信息表（存储存钱和取钱的记录）
-- 转账信息表（存储转账信息记录）
-- 状态信息变化表（存储银行卡状态：1正常；2挂失；3冻结；4注销；5睡眠；）

--------------------
-- 创建库
--------------------
create database Bank;
go

use Bank;
go



--------------------
-- 创建表
-------------------
-- 创建账号信息表
create table AccountInfo(
	AccountId int primary key identity(1, 1), -- 账户编号
	AccountCode varchar(20) not null, -- 身份证号码
	AccountPhone varchar(20) not null, -- 电话号码
	RealName varchar(20) not null, -- 真实姓名
	OpenTime smalldatetime not null, -- 开户时间
);
-- 创建银行表
create table BankCard(
	CardNo varchar(30) primary key, -- 银行卡号
	AccountId int not null,
	CardPwd varchar(30) not null, -- 银行密码
	CardBalance money not null default(0.00), -- 银行卡余额
	CardState tinyint not null default(1), -- 银行卡状态
	CardTime smalldatetime default(getdate()) -- 开卡时间
);
-- 创建交易信息表
create table CardExchange(
	ExchangeId int primary key identity(1, 1),
	CardNo varchar(30) not null, -- 银行卡号（与银行卡号形成主外键关系）
	MoneyInBank money not null, -- 存钱金额
	MoneyOutBank money not null, -- 取钱金额
	ExchangeTime smalldatetime not null, -- 交易时间 
);
-- 创建转账信息表
create table CardTransfer(
	TransferId int primary key identity(1, 1), -- 转账id
	CardNoOut varchar(30) not null, -- 转出银行卡号（与银行卡表形成主外键关系）
	CardNoIn varchar(30) not null, -- 转入银行卡号（与银行卡表形成主外键关系）
	TransferMoney money not null, -- 转账金额
	TransferTime smalldatetime not null, -- 转账时间
);
-- 创建状态信息变化表
create table CardStateChange(
	StateId int primary key identity(1, 1), -- 自增id 
	CardNo varchar(30) not null, -- 银行卡号（与银行卡表形成主外键关系）
	OldState tinyint not null, -- 银行卡原始状态
	NewState tinyint not null, -- 银行卡新状态
	StateWhy nvarchar(200) not null, -- 状态变化原因
	StateTime smalldatetime not null, -- 状态变化时间
);
go


--------------------
-- 增加约束
-------------------
alter table BankCard add constraint CK_BankCard_CardBalance check(CardBalance >= 0)



--------------------
-- 为刘备，关羽，张飞执行开卡开户的操作
-------------------
-- 刘备身份证：420107198905064135
-- 关羽身份证：420107199507104133
-- 张飞身份证：420107199602034138
insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107198905064135', '13554785425', '刘备', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544587', 1, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544588', 1, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544589', 1, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199507104133', '13454788854', '关羽', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547858741263', 2, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547858741264', 2, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199602034138', '13456896321', '张飞', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547854125656', 3, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547854125657', 3, '123456', 0, 1);

--------------------
-- 进行存钱操作，刘备存储2000元，关羽存钱8000元，张飞存钱50万。
-------------------
-- select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
-- select * from CardExchange;
update BankCard set CardBalance = CardBalance + 2000 where CardNo = '6225125478544587';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478544587', 2000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 8000 where CardNo = '6225547858741263';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225547858741263', 8000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 500000 where CardNo = '6225547854125656';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225547854125656', 500000, 0, GETDATE());


--------------------
-- 进行转账操作，刘备给张飞转1000元
-------------------
-- select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
-- select * from CardTransfer;
update BankCard set CardBalance = CardBalance - 1000 where CardNo = '6225125478544587';
update BankCard set CardBalance = CardBalance + 1000 where CardNo = '6225547854125656';
insert into CardTransfer (CardNoOut, CardNoIn, TransferMoney, TransferTime) 
	values ('6225125478544587', '6225547854125656', 1000, GETDATE());


--------------------
-- 初始化其他人账户信息
-------------------
insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199703035139', '13854785435', '赵云', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478545001', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199706106240', '13954786546', '马超', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478546002', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107198006107351', '13654787435', '黄忠', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478547112', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107200006108412', '13654787566', '诸葛亮', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478548233', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107200006109523', '13654787677', '马良', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478549344', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107200006120634', '13654788789', '法正', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478560455', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199908122234', '13854788790', '刘封', GETDATE());




--------------------
-- 进行存钱操作，赵云存储5000元，马超存钱200000元，黄忠存钱50000万，诸葛亮存钱30000。
-------------------
update BankCard set CardBalance = CardBalance + 5000 where CardNo = '6225125478545001';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478545001', 5000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 200000 where CardNo = '6225125478546002';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478546002', 200000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 50000 where CardNo = '6225125478547112';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478547112', 50000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 30000 where CardNo = '6225125478548233';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478548233', 30000, 0, GETDATE());

--------------------
-- 进行取钱操作，马超取钱10000元，诸葛亮取钱3000。
-------------------
update BankCard set CardBalance = CardBalance - 10000 where CardNo = '6225125478546002';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478546002', 0, 10000, GETDATE());

update BankCard set CardBalance = CardBalance - 3000 where CardNo = '6225125478548233';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478548233', 0, 3000, GETDATE());

--------------------
-- 进行转账操作，张飞给黄忠转账50000，
-------------------
-- select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
-- select * from CardTransfer;
update BankCard set CardBalance = CardBalance - 50000 where CardNo = '6225547854125656';
update BankCard set CardBalance = CardBalance + 50000 where CardNo = '6225125478547112';
insert into CardTransfer (CardNoOut, CardNoIn, TransferMoney, TransferTime) 
	values ('6225547854125656', '6225125478547112', 50000, GETDATE());


--------------------
-- 进行冻结操作，马良的卡被冻结了
-------------------
update BankCard set CardState = 3 where CardNo = '6225125478549344'
insert into CardStateChange (CardNo,OldState, NewState, StateWhy, StateTime) 
	values ('6225125478549344', 1, 3, '进行走私，冻结账户', GETDATE());


select * from AccountInfo
select * from BankCard
select * from CardExchange
select * from CardTransfer
select * from CardStateChange

print '执行成功'




--查询所有的账户 AccountInfo 信息。
select * from AccountInfo;

--查询所有的银行卡 BankCard 信息。
select * from BankCard;

--查询所有的交易 CardExchange 信息。
select * from CardExchange;

--查询所有的转账 CardTransfer 信息。
select * from CardTransfer;

--查询所有的状态变更 CardStateChange 信息。
select * from CardStateChange;

--查询状态是正常的银行卡信息，显示卡号和余额。
select CardNo,CardBalance from BankCard where CardState ='1';

--查看总开户数。
select * from AccountInfo;
select count(0) from AccountInfo;

--查询总开卡数。
select * from BankCard;
select count(0) from BankCard;


--查询刘备有几张银行卡。
select * from AccountInfo;
select * from BankCard where AccountId = 1;
select count(0) from BankCard where AccountId = 1;

--查询每个账户的银行卡数，显示AccountId和银行卡数量。
select * from BankCard; 
select count(0) as 银行卡数量 from BankCard group by AccountId;
select AccountId,count(0) as 银行卡数量 from BankCard group by AccountId;


--查询出银行卡数最多的账户，显示AccountId和银行卡数量。
select top 1 AccountId,count(0) as 银行卡数量 from BankCard group by AccountId order by 银行卡数量 desc;


--查询 6225125478545001 卡号的交易记录。
select * from CardExchange;
select * from CardExchange where CardNo = '6225125478545001';
select count(0) as 交易记录 from CardExchange where CardNo = '6225125478545001';

--查询 6225547854125656 的转账记录。
select * from CardTransfer where CardNoIn = '6225547854125656'or CardNoOut ='6225547854125656';
select count(0) as 转账记录 from CardTransfer where CardNoIn = '6225547854125656'or CardNoOut ='6225547854125656';

--查询存钱最大的一笔记录，显示卡号和金额。
select * from CardExchange ;
select top 1 CardNo ,max(MoneyInBank) 金额 from CardExchange group by CardNo order by 金额 desc;

--查询有存过钱的银行卡信息，显示卡号和余额。
select * from BankCard;
select * from CardExchange;
select CardNo,MoneyInBank  from CardExchange where MoneyInBank > 0 group by MoneyInBank,CardNo;
select CardBalance from BankCard where CardNo in (6225125478544587,6225125478545001,6225547858741263,6225125478548233,6225125478547112,6225125478546002,6225547854125656);

--or
select  BankCard . CardNo, BankCard . CardBalance from BankCard
	inner join CardExchange on BankCard.CardNo = CardExchange.CardNo where MoneyInBank>0;

--查询有取过钱的银行卡信息，显示卡号和余额。
select CardNo,MoneyOutBank  from CardExchange where MoneyOutBank > 0 group by MoneyOutBank,CardNo;
select CardBalance from BankCard where CardNo in (6225125478548233,6225125478546002);

--or
select BankCard . CardNo, BankCard . CardBalance from BankCard
	inner join CardExchange on BankCard.CardNo = CardExchange . CardNo where MoneyOutBank>0;
 
--查询没有交易记录的银行卡信息，显示卡号。
select * from CardExchange where  MoneyInBank='0' and MoneyOutBank ='0';

--查询有转账记录的银行卡信息，显示卡号和余额。
select TransferId from CardTransfer where TransferMoney > 0;
select  BankCard . CardNo, BankCard . CardBalance from BankCard 
	where CardNo in (select CardNoOut from CardTransfer);
  
